package dbhelp

import (
	"database/sql"
	"errors"
	"fmt"
	"reflect"
	"strconv"
	"strings"
)

//更新记录
//Demo---------------******事例很简单吧，也很好用*************-------------------
//lDataSet := dbhelp.CreateNewDataSet()
//lDataSet.LoadFromRows(lRows)
//加载数据正确
//if lDataSet.Active {
//	lDataSet.First()
//	fmt.Println(lDataSet.RowsCount)
//	for lDataSet.Next() {
//		fmt.Println("第" + strconv.FormatInt(lDataSet.RowsIndex, 10))
//		fmt.Println(lDataSet.FieldByName("FID").AsString())
//		fmt.Println(lDataSet.FieldByName("FBigInt").AsInt64())
//		fmt.Println(lDataSet.FieldByName("FInt").AsInt64())
//	}
//}
type TMyOpenFunc func(QDataSet *TDataSet) bool
type TFieldKind uint

const (
	FieldKindString TFieldKind = iota
	FieldKindInt64
	FieldKindFloat64
	FieldKindBool
	FieldKindTime
	FieldKindByte
	FieldKindRowState
)

//数据行状态 RowStateAdd添加,RowStateSelect查询,RowStateDel删除,RowStateUnknown未知
type TRowState uint

const (
	RowStateAdd TRowState = iota
	RowStateSelect
	RowStateEdit
	RowStateDel
	RowStateUnknown
)

type TDataSetUpdateMode uint

//UpdateKeyOnly 只以主键更新,UpdateWhereAll所有字段参与条件更新,updateWhereChange变化的字段参与条件更新
const (
	UpdateKeyOnly TDataSetUpdateMode = iota
	UpdateWhereAll
	updateWhereChange
)

type TFieldUpdateMode uint

// FieldKey 参与where运算,FieldWhere 参与 where运算,FieldUpdate只参与更新运算，FieldNull此字段不做处理
const (
	FieldKey TFieldUpdateMode = iota
	FieldWhere
	FieldUpdate
	FieldNull
)

//每行数据组成
type TDataRow struct {
	ownerDataSet *TDataSet
	//每行的值
	RowValue []interface{}
	//每行的状态
	RowState TRowState
}

type TField struct {
	ownerDataSet    *TDataSet
	FieldIndex      int
	FieldName       string
	FieldJsonName   string
	FieldKind       TFieldKind
	FieldSize       int64 //字符串长度
	FieldPrecision  int64 //精度
	Fieldscale      int64 //小数位
	FieldUpdateMode TFieldUpdateMode
}
type TDataSet struct {
	DataSetName       string
	TableName         string
	ProcedureName     string
	Primarykey        string
	cachedUpdates     bool
	DataSetUpdateMode TDataSetUpdateMode
	//是否正确打开
	Active bool
	//字段相关
	FieldsCount int
	Fields      []*TField
	FieldsMap   map[string]*TField
	//值相关
	RowsIndex     int64
	RowsCount     int64
	DataSetValues []*TDataRow
	DataSetCache  []*TDataRow
	//分页用到的属笥
	PageSize int
	PageNow  int
}

//TDataRow
func CreateNewDataRow(QDataSet *TDataSet) *TDataRow {
	lDataRow := &TDataRow{ownerDataSet: QDataSet, RowValue: []interface{}{}, RowState: RowStateUnknown}
	return lDataRow
}

//TField
func (QField *TField) checkOwnDataSet() bool {
	if QField.ownerDataSet == nil {
		panic("字段无关联的数据集,无法取值")
	}
	if !QField.ownerDataSet.Active {
		panic("数据集未打开,请先打开数据集")
	}
	if QField.ownerDataSet.RowsCount <= 0 {
		panic("无相关数据,取值失败")
	}
	//打开数据默认是-1，如有值默认取第一条
	if QField.ownerDataSet.RowsIndex < 0 {
		QField.ownerDataSet.RowsIndex = 0
	}
	return true
}

//QKeepNull true保持null值,false 取 null对应默认值
//字符串 "",数字 0, 时间 0
func (QField *TField) AsValue() interface{} {
	QField.checkOwnDataSet()
	lDataSet := QField.ownerDataSet
	//取二维数组对应的行数值
	lValue := lDataSet.DataSetValues[lDataSet.RowsIndex].RowValue[QField.FieldIndex]
	switch QField.FieldKind {
	case FieldKindString:
		lNullString, lok := lValue.(*sql.NullString)
		if lok {
			if !lNullString.Valid {
				return ""
			} else {
				return lNullString.String
			}
		}
	case FieldKindInt64:
		lNullInt64, lok := lValue.(*sql.NullInt64)
		if lok {
			if !lNullInt64.Valid {
				return 0
			} else {
				return lNullInt64.Int64
			}
		}
	case FieldKindFloat64:
		lNullFloat64, lok := lValue.(*sql.NullFloat64)
		if lok {
			if !lNullFloat64.Valid {
				return 0
			} else {
				return lNullFloat64.Float64
			}
		}
	case FieldKindBool:
		lNullBool, lok := lValue.(*sql.NullBool)
		if lok {
			if !lNullBool.Valid {
				return false
			} else {
				return lNullBool.Bool
			}
		}
	case FieldKindTime:
		lNullTime, lok := lValue.(*sql.NullTime)
		if lok {
			if !lNullTime.Valid {
				return 0
			} else {
				return lNullTime.Time
			}
		}
	case FieldKindByte:
		lbyte, _ := lValue.(*[]byte)
		return lbyte
	default:
		panic("未知的类型")
	}
	return nil
}

//保持值的格式
//返回 &sql.NullString{},....等对应的值格式
func (QField *TField) AsSQLValue(QKeepNull bool) interface{} {
	QField.checkOwnDataSet()
	lDataSet := QField.ownerDataSet
	//取二维数组对应的行数值
	return lDataSet.DataSetValues[lDataSet.RowsIndex].RowValue[QField.FieldIndex]
}

//设置值
func (QField *TField) SetValue(QValue interface{}) {
	QField.checkOwnDataSet()
	lDataSet := QField.ownerDataSet
	lRow := lDataSet.DataSetValues[lDataSet.RowsIndex]
	switch QField.FieldKind {
	case FieldKindString:
		lNullString, lok := lRow.RowValue[QField.FieldIndex].(*sql.NullString)
		if lok {
			error := lNullString.Scan(QValue)
			if error != nil {
				panic(error)
			}
		}
	case FieldKindInt64:
		lNullInt64, lok := lRow.RowValue[QField.FieldIndex].(*sql.NullInt64)
		if lok {
			error := lNullInt64.Scan(QValue)
			if error != nil {
				panic(error)
			}
		}
	case FieldKindFloat64:
		lNullFloat64, lok := lRow.RowValue[QField.FieldIndex].(*sql.NullFloat64)
		if lok {
			error := lNullFloat64.Scan(QValue)
			if error != nil {
				panic(error)
			}
		}
	case FieldKindBool:
		lNullBool, lok := lRow.RowValue[QField.FieldIndex].(*sql.NullBool)
		if lok {
			error := lNullBool.Scan(QValue)
			if error != nil {
				panic(error)
			}
		}
	case FieldKindTime:
		lNullTime, lok := lRow.RowValue[QField.FieldIndex].(*sql.NullTime)
		if lok {
			error := lNullTime.Scan(QValue)
			if error != nil {
				panic(error)
			}
		}
	case FieldKindByte:
		lNullBool, lok := lRow.RowValue[QField.FieldIndex].(*sql.NullBool)
		if lok {
			error := lNullBool.Scan(QValue)
			if error != nil {
				panic(error)
			}
		}
	default:
		panic("未知的类型")
	}
	if lDataSet.cachedUpdates {

	}
}

//强制转成字符串
func (QField *TField) AsString() string {
	QField.checkOwnDataSet()
	lDataSet := QField.ownerDataSet
	lRow := lDataSet.DataSetValues[lDataSet.RowsIndex]
	lValue := lRow.RowValue[QField.FieldIndex]
	switch QField.FieldKind {
	case FieldKindString:
		lNullString, lok := lValue.(*sql.NullString)
		if lok {
			if !lNullString.Valid {
				return ""
			} else {
				return lNullString.String
			}
		}
	case FieldKindInt64:
		lNullInt64, lok := lValue.(*sql.NullInt64)
		if lok {
			if !lNullInt64.Valid {
				return "0"
			} else {
				return strconv.FormatInt(lNullInt64.Int64, 10)
			}
		}
	case FieldKindFloat64:
		lNullFloat64, lok := lValue.(*sql.NullFloat64)
		if lok {
			if !lNullFloat64.Valid {
				return "0"
			} else {
				return strconv.FormatFloat(lNullFloat64.Float64, 'f', -1, 64)
			}
		}
	case FieldKindBool:
		lNullBool, lok := lValue.(*sql.NullBool)
		if lok {
			if !lNullBool.Valid {
				return "false"
			} else {
				return strconv.FormatBool(lNullBool.Bool)
			}
		}
	case FieldKindTime:
		lNullTime, lok := lValue.(*sql.NullTime)
		if lok {
			if !lNullTime.Valid {
				return "0"
			} else {
				return lNullTime.Time.Format("2006-01-02 15:04:05")
			}
		}
	case FieldKindByte:
		lbyte, _ := lValue.([]byte)
		return string(lbyte)
	default:
		panic("未知的类型")
	}
	return ""
}

//设置字符串
func (QField *TField) SetString(QValue string) {
	QField.checkOwnDataSet()
}

//强制转化成浮点型
func (QField *TField) AsFloat() float64 {
	QField.checkOwnDataSet()
	lDataSet := QField.ownerDataSet
	lRow := lDataSet.DataSetValues[lDataSet.RowsIndex]
	lValue := lRow.RowValue[QField.FieldIndex]
	switch QField.FieldKind {
	case FieldKindString:
		panic("不支持的类型")
	case FieldKindInt64:
		lNullInt64, lok := lValue.(*sql.NullInt64)
		if lok {
			if !lNullInt64.Valid {
				return 0
			} else {
				return float64(lNullInt64.Int64)
			}
		}
	case FieldKindFloat64:
		lNullFloat64, lok := lValue.(*sql.NullFloat64)
		if lok {
			if !lNullFloat64.Valid {
				return 0
			} else {
				return lNullFloat64.Float64
			}
		}
	case FieldKindBool, FieldKindTime, FieldKindByte:
		panic("不支持的类型")
	default:
		panic("未知的类型")
	}
	return 0
}

//设置字符串值
func (QField *TField) SetFloat(QValue float64) {
	QField.checkOwnDataSet()
}

//强制转化在整型
func (QField *TField) AsInt64() int64 {
	QField.checkOwnDataSet()
	lDataSet := QField.ownerDataSet
	lRow := lDataSet.DataSetValues[lDataSet.RowsIndex]
	lValue := lRow.RowValue[QField.FieldIndex]
	switch QField.FieldKind {
	case FieldKindString:
		panic("不支持的类型")
	case FieldKindInt64:
		lNullInt64, lok := lValue.(*sql.NullInt64)
		if lok {
			if !lNullInt64.Valid {
				return 0
			} else {
				return lNullInt64.Int64
			}
		}
	case FieldKindFloat64:
		panic("不支持的类型")
	case FieldKindBool, FieldKindTime, FieldKindByte:
		panic("不支持的类型")
	default:
		panic("未知的类型")
	}
	return 0
	return 0
}

//设置整型值
func (QField *TField) SetInt64(QValue int64) {
	QField.checkOwnDataSet()
}

//GlobalOpenList分页形式打开数据,
//GlobalOpenPackList分页形式打开数据，然后取所有数据
//GlobalOpenData取所有数据
//GlobalOpenProcedure以存储过程方式打开数据
//GlobalSaveData保存数据
var GlobalOpenData, GlobalOpenList, GlobalOpenPackList, GlobalOpenProcedure, GlobalSaveData TMyOpenFunc = nil, nil, nil, nil, nil

//TDataSet
func CreateNewDataSet() *TDataSet {
	lDataSet := &TDataSet{}
	//是否要开启本地缓存,本地缓存开启后每行数据多了个数据状态字段 FieldRowState
	lDataSet.cachedUpdates = false
	lDataSet.DataSetUpdateMode = UpdateKeyOnly
	return lDataSet
}

func (QDataSet *TDataSet) initDataSet() bool {
	QDataSet.Active = false
	QDataSet.RowsIndex = -1
	QDataSet.RowsCount = 0
	QDataSet.FieldsCount = 0
	QDataSet.DataSetValues = []*TDataRow{}
	QDataSet.Fields = []*TField{}
	QDataSet.FieldsMap = make(map[string]*TField)
	QDataSet.DataSetCache = nil
	QDataSet.SetCachedUpdates(QDataSet.cachedUpdates)
	return true
}

func (QDataSet *TDataSet) initRow() (*TDataRow, error) {
	lRow := CreateNewDataRow(QDataSet)
	for _, item := range QDataSet.Fields {
		switch item.FieldKind {
		case FieldKindString:
			lNullString := &sql.NullString{}
			lRow.RowValue = append(lRow.RowValue, lNullString)
		case FieldKindInt64:
			lNullInt64 := &sql.NullInt64{}
			lRow.RowValue = append(lRow.RowValue, lNullInt64)
		case FieldKindFloat64:
			lNullFloat64 := &sql.NullFloat64{}
			lRow.RowValue = append(lRow.RowValue, lNullFloat64)
		case FieldKindBool:
			lNullBool := &sql.NullBool{}
			lRow.RowValue = append(lRow.RowValue, lNullBool)
		case FieldKindTime:
			lNullTime := &sql.NullTime{}
			lRow.RowValue = append(lRow.RowValue, lNullTime)
		case FieldKindByte:
			lbyte := []byte{}
			lRow.RowValue = append(lRow.RowValue, &lbyte)
		case FieldKindRowState:
		default:
			return nil, errors.New("未定义的字段类型(FieldKind),请完善")
		}
	}
	return lRow, nil
}

//从Row加载结果集
func (QDataSet *TDataSet) LoadFromRows(rows *sql.Rows) error {
	if !QDataSet.initDataSet() {
		return errors.New("初始化数据集失败")
	}
	//开始加载加载成功Active=true
	columns, err := rows.Columns()
	if err != nil {
		return err
	}
	columnTypes, err := rows.ColumnTypes()
	if err != nil {
		fmt.Println(columnTypes)
		return err
	}
	defer func() {
		if err := recover(); err != nil {
			QDataSet.initDataSet()
		}
	}()
	//字段加载
	for iIndex, item := range columnTypes {
		lField := &TField{}
		lField.ownerDataSet = QDataSet
		QDataSet.Fields = append(QDataSet.Fields, lField)
		QDataSet.FieldsCount = QDataSet.FieldsCount + 1
		lField.FieldIndex = iIndex
		columnName := columns[iIndex]
		lField.FieldName = columnName
		QDataSet.FieldsMap[columnName] = lField
		filetype := item.ScanType()
		switch filetype.Kind() {
		case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
			lField.FieldKind = FieldKindInt64
		case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
			lField.FieldKind = FieldKindInt64
		case reflect.Float32, reflect.Float64:
			lField.FieldKind = FieldKindFloat64
		case reflect.String:
			lField.FieldKind = FieldKindString
			ilen, iok := item.Length()
			if iok {
				lField.FieldSize = ilen
			}
		case reflect.Array, reflect.Slice:
			switch filetype.Elem().Kind() {
			case reflect.Uint8:
				switch item.DatabaseTypeName() {
				case "DECIMAL":
					lField.FieldKind = FieldKindFloat64
					iprecision, iscale, iok := item.DecimalSize()
					if iok {
						lField.FieldPrecision = iprecision
						lField.Fieldscale = iscale
					}
				case "BINARY":
					lField.FieldKind = FieldKindByte
				default:
					return errors.New("未定义的类型,请完善->" + filetype.Elem().Kind().String() + "->" + item.DatabaseTypeName())
				}
			default:
				return errors.New("未定义的类型,请完善->" + filetype.Elem().Kind().String() + "->" + item.DatabaseTypeName())
			}
		case reflect.Struct:
			if filetype.ConvertibleTo(TimeType) {
				lField.FieldKind = FieldKindTime
			} else {
				return errors.New("未定义的类型,请完善->" + filetype.Kind().String() + "->" + item.DatabaseTypeName())
			}
		case reflect.Bool:
			lField.FieldKind = FieldKindBool
		default:
			return errors.New("未定义的类型,请完善->" + filetype.Kind().String() + "->" + item.DatabaseTypeName())
		}
	}
	//状态行加载
	//值加载
	for rows.Next() {
		lRow, err := QDataSet.initRow()
		if err != nil {
			return err
		}
		errScan := rows.Scan(lRow.RowValue...)
		if errScan != nil {
			return errScan
		}
		lRow.RowState = RowStateSelect
		QDataSet.DataSetValues = append(QDataSet.DataSetValues, lRow)
		QDataSet.RowsCount = QDataSet.RowsCount + 1
	}
	QDataSet.Active = true
	return nil
}

//分析Json转成数据集
func (QDataSet *TDataSet) LoadFromJson(QJsonStr string) error {
	return nil
}

//是否开启缓存
func (QDataSet *TDataSet) SetCachedUpdates(QBool bool) {
	if QBool {
		if QDataSet.DataSetCache == nil {
			QDataSet.DataSetCache = []*TDataRow{}
		}
	} else {
		QDataSet.DataSetCache = nil
	}
	QDataSet.cachedUpdates = QBool
}

//跟据字段名称取字段,区分大小写
func (QDataSet *TDataSet) FieldByName(QFieldName string) *TField {
	if QDataSet.FieldsCount <= 0 {
		return nil
	}
	return QDataSet.FieldsMap[QFieldName]
}

//跟据字段名称取字段,不区分大小写,不建议用,坏习惯
func (QDataSet *TDataSet) FieldByNameNoCase(QFieldName string) *TField {
	if QDataSet.FieldsCount <= 0 {
		return nil
	}
	lFieldName := strings.ToLower(QFieldName)
	for _, item := range QDataSet.Fields {
		if strings.ToLower(item.FieldName) == lFieldName {
			return item
			break
		}
	}
	return nil
}

//跟据索引获取字段
func (QDataSet *TDataSet) FieldByIndex(QIndex int) *TField {
	if QIndex < 0 || QIndex >= QDataSet.FieldsCount {
		return nil
	}
	return QDataSet.Fields[QIndex]
}

//移到第一条
func (QDataSet *TDataSet) First() {
	//当调用First指向是 -1
	//Next才可从0开始遍历
	QDataSet.RowsIndex = -1
}

//移到下一条
func (QDataSet *TDataSet) Next() bool {
	if QDataSet.RowsCount > 0 {
		if QDataSet.RowsIndex+1 < QDataSet.RowsCount {
			QDataSet.RowsIndex = QDataSet.RowsIndex + 1
			return true
		} else {
			return false
		}
	} else {
		return false
	}
}

//移到最后一条
func (QDataSet *TDataSet) Last() {
	if QDataSet.RowsCount > 0 {
		QDataSet.RowsIndex = QDataSet.RowsCount - 1
	} else {
		QDataSet.RowsIndex = -1
	}
}

func (QDataSet *TDataSet) Append() {
	if !QDataSet.Active {
		panic("数据集未打开")
	}
	lRow, err := QDataSet.initRow()
	if err != nil {
		panic(err)
	}
	lRow.RowState = RowStateAdd
	//添加到最后一行
	QDataSet.DataSetValues = append(QDataSet.DataSetValues, lRow)
	QDataSet.RowsCount = QDataSet.RowsCount + 1
	//指向最后一条
	QDataSet.RowsIndex = QDataSet.RowsCount - 1
}

//删除当前一条
func (QDataSet *TDataSet) Del() (bool, error) {
	if !QDataSet.Active {
		return false, errors.New("数据集未打开")
	}
	if QDataSet.RowsCount <= 0 {
		return false, errors.New("记录为空")
	}
	if QDataSet.RowsIndex < 0 {
		QDataSet.RowsIndex = 0
	}
	QDataSet.DataSetValues = append(QDataSet.DataSetValues[:QDataSet.RowsIndex+1], QDataSet.DataSetValues[QDataSet.RowsIndex+2:]...)
	QDataSet.RowsCount = QDataSet.RowsCount - 1
	if QDataSet.RowsIndex == QDataSet.RowsCount {
		QDataSet.RowsIndex = QDataSet.RowsIndex - 1
	}
	return true, nil
}

//删除最后一条,RowIndex不变，但如果最后一条向前移一下
func (QDataSet *TDataSet) DelLast() (bool, error) {
	if !QDataSet.Active {
		return false, errors.New("数据集未打开")
	}
	if QDataSet.RowsCount <= 0 {
		return false, errors.New("记录为空")
	}
	//删除最后一个元素
	QDataSet.DataSetValues = QDataSet.DataSetValues[:QDataSet.RowsCount-1]
	//删除最后一条
	QDataSet.RowsCount = QDataSet.RowsCount - 1
	if QDataSet.RowsIndex == QDataSet.RowsCount {
		QDataSet.RowsIndex = QDataSet.RowsIndex - 1
	}
	return true, nil
}

//删除某条
func (QDataSet *TDataSet) DelIndex(QIndex int64) (bool, error) {
	if !QDataSet.Active {
		return false, errors.New("数据集未打开")
	}
	if QDataSet.RowsCount <= 0 {
		return false, errors.New("记录为空")
	}
	if QIndex < 0 || QIndex > QDataSet.RowsCount {
		return false, errors.New("删除的索引范围不正确")
	}
	if QDataSet.RowsIndex < 0 {
		QDataSet.RowsIndex = 0
	}
	QDataSet.DataSetValues = append(QDataSet.DataSetValues[:QDataSet.RowsIndex+1], QDataSet.DataSetValues[QDataSet.RowsIndex+2:]...)
	QDataSet.RowsCount = QDataSet.RowsCount - 1
	if QDataSet.RowsIndex == QDataSet.RowsCount {
		QDataSet.RowsIndex = QDataSet.RowsIndex - 1
	}
	return true, nil
}

//清除数据
func (QDataSet *TDataSet) Clear() bool {
	return QDataSet.initDataSet()
}

//打开数据
func (QDataSet *TDataSet) OpenData() bool {
	if GlobalOpenData != nil {
		return GlobalOpenData(QDataSet)
	} else {
		return false
	}
}

//以分页形式打开数据
func (QDataSet *TDataSet) OpenList() bool {
	if GlobalOpenData != nil {
		return GlobalOpenList(QDataSet)
	} else {
		return false
	}
}

//以分页形式打开数据，获取所有包的数据
func (QDataSet *TDataSet) OpenPackList() bool {
	if GlobalOpenData != nil {
		return GlobalOpenPackList(QDataSet)
	} else {
		return false
	}
}

//以存储过程打开数据
func (QDataSet *TDataSet) OpenProcedure() bool {
	if GlobalOpenData != nil {
		return GlobalOpenProcedure(QDataSet)
	} else {
		return false
	}
}

//保存数据
func (QDataSet *TDataSet) SaveData() bool {
	if GlobalOpenData != nil {
		return GlobalSaveData(QDataSet)
	} else {
		return false
	}
}
